package com.example.mytayunapi.Mapper;

import com.example.mytayunapi.Entity.productsInfo;
import com.example.mytayunapi.Vo.RefundProductsOrders;
import com.example.mytayunapi.Vo.UserProductImage;
import org.apache.ibatis.annotations.*;

import java.util.List;
import java.util.Map;

@Mapper
public interface productsInfoMapper {
//    @Select("SELECT * FROM tayun.products")
//    List<productsInfo> getProductsInfo();

 @Delete("delete from refunds where Refund_id=#{Refund_id}")
 Integer deleteRefund(@Param("Refund_id") String Refund_id);

 @Select("SELECT " +
         "p.Product_id  , " +
         "p.Product_image , " +
         "p.Product_name , " +
         "p.Product_price , " +
         "p.category, " +
         "p.subcategory, " +
         "p.Product_detail , " +
         "r.Refund_status  ," +
         "r.Refund_id " +
         "FROM " +
         "products p " +
         "JOIN " +
         "orders o ON p.Product_id = o.Product_id " +
         "JOIN " +
         "Refunds r ON o.order_id = r.order_id " +
         "WHERE " +
         "o.School_degree = #{School_degree} " +
         "ORDER BY " +
         "o.order_time DESC")
 List<RefundProductsOrders> findRefundProductsOrdersBySchoolDegree(@Param("School_degree") String School_degree);


 @Select("SELECT * FROM products WHERE category = #{category} and  release_status= '在售'")
 List<productsInfo> findByCategoryAndstatus(String category);

 @Select("SELECT * FROM products WHERE category = #{category}")
 List<productsInfo> findByCategory(String category);

 @Select("SELECT * FROM products WHERE Release_status = '下架'")
 List<productsInfo> getDownProducts();

 @Update("update products set Release_status = '下架',operator=#{operator}  where Product_id = #{Product_id}")
 Integer updateProductStatus(productsInfo productsinfo);

 //通过状态查询信息（郭）
 @Select("SELECT * FROM products WHERE Release_status = '在售'")
 List<productsInfo> getInfoByReleaseStatus();

 @Select("SELECT * FROM products where Release_status = #{Release_status}")
 List<productsInfo> getProductsInfoByReleaseStatus(String Release_status);

 @Insert("insert into products(Nick_name,Product_name,Product_price,category,Product_detail,Product_image,School_degree,Release_status,Product_status,beizhu) " +
         "values(#{Nick_name},#{Product_name},#{Product_price},#{category},#{Product_detail},#{Product_image},#{School_degree},#{Release_status},#{Product_status},#{beizhu})")
 Integer insertProductsInfo(productsInfo productsinfo);

 @Select("SELECT products.*,user.Nick_name FROM products JOIN user ON products.School_degree = user.School_degree LEFT JOIN ProductImages pi ON products.Product_id = pi.Product_id")
 List<UserProductImage> getImageUserProduct();

 @Select("SELECT p.*, u.Nick_name, MIN(pi.Product_image) AS product_image  FROM products p JOIN user u ON p.School_degree = u.School_degree LEFT JOIN ProductImages pi ON p.Product_id = pi.Product_id WHERE p.Product_id = #{Product_id} GROUP BY p.Product_id  ")
 List<UserProductImage> getImageUserProductId(Integer Product_id);

 @Update("update products set Release_status = #{Release_status},beizhu = #{beizhu},operator=#{operator} where Product_id=#{Product_id}")
 Integer updateProductsInfoByProductId(UserProductImage productsStatusInfo);

 @Select("SELECT products.*,user.Nick_name FROM products JOIN user ON products.School_degree = user.School_degree LEFT JOIN ProductImages pi ON products.Product_id = pi.Product_id where Release_status = #{Release_status}")
 List<UserProductImage> getImageUserProductByReleaseStatus(String Release_status);

 @Select("SELECT products.*,user.Nick_name FROM products JOIN user ON products.School_degree = user.School_degree LEFT JOIN ProductImages pi ON products.Product_id = pi.Product_id where Release_status != #{Release_status}")
 List<UserProductImage> getImageUserProductByReleaseStatusNot(String Release_status);

 @Select("SELECT products.*,user.Nick_name FROM products JOIN user ON products.School_degree = user.School_degree LEFT JOIN ProductImages pi ON products.Product_id = pi.Product_id where Release_status = #{Release_status} and products.Product_id = #{Product_id}")
 List<UserProductImage> getImageUserProductByReleaseStatusAndId(@Param("Release_status") String Release_status, @Param("Product_id") Integer Product_id);


 @Select("SELECT products.*,user.Nick_name FROM products JOIN user ON products.School_degree = user.School_degree LEFT JOIN ProductImages pi ON products.Product_id = pi.Product_id where Product_name LIKE CONCAT('%', #{keyword}, '%') " +
         "and products.category = #{category} and products.subcategory = #{subcategory} and Release_status = '审核中'")
 List<UserProductImage> getImageUserProductByReleaseStatusAndIdProAcate(@Param("category")String category,@Param("subcategory")String subcategory,@Param("keyword")String keyword);

 @Select("SELECT products.*,user.Nick_name FROM products JOIN user ON products.School_degree = user.School_degree LEFT JOIN ProductImages pi ON products.Product_id = pi.Product_id where Release_status = '审核中'")
 List<UserProductImage> getImageUserProductByRelease();

 @Select("SELECT products.*,user.Nick_name FROM products JOIN user ON products.School_degree = user.School_degree LEFT JOIN ProductImages pi ON products.Product_id = pi.Product_id where category = #{category}")
 List<UserProductImage> getImageUserProductByCategory(String category);

 @Select("SELECT products.*,user.Nick_name FROM products JOIN user ON products.School_degree = user.School_degree LEFT JOIN ProductImages pi ON products.Product_id = pi.Product_id where subcategory = #{subcategory}")
 List<UserProductImage> getImageUserProductBySubcategory(String subcategory);

 @Select("SELECT products.*,user.Nick_name FROM products JOIN user ON products.School_degree = user.School_degree LEFT JOIN ProductImages pi ON products.Product_id = pi.Product_id where Product_name LIKE CONCAT('%', #{keyword}, '%')")
 List<UserProductImage> getImageUserProductByKeyword(String keyword);
//    @Select("SELECT p.*,u.name AS Nick_name, pi.Product_image AS product_image FROM products p JOIN user u ON p.School_degree = u.School_degree LEFT JOIN ProductImages pi ON p.Product_id = pi.Product_id WHERE p.Product_name LIKE CONCAT('%${Product_name}%') ORDER BY DATEDIFF(CURDATE(), #{p.Product_time}) DESC")
//    List<UserProductImage> getProductsInfoByProductName(String Product_name);
 //

 @Insert("insert into products(Product_name,Product_price,category,subcategory,Product_detail,Product_image,School_degree,Product_status)\n" +
         "values(#{Product_name},#{Product_price},#{category},#{subcategory},#{Product_detail},#{Product_image},#{School_degree},#{Product_status})")
 Integer insertProducts(productsInfo productsinfo);

 @Select("select * from products p join user u on p.School_degree = u.School_degree where p.School_degree = (select School_degree from products where Product_id = #{Product_id}) and Release_status = '在售'")
 List<UserProductImage> getmaijiaProductByProductId(Integer Product_id);

 @Insert("INSERT INTO products(Product_name, Product_price, category, subcategory, Product_detail, Product_image, School_degree, Product_status,Release_status) " +
         "VALUES(#{productName}, #{productPrice}, #{category}, #{subcategory}, #{productDetail}, #{productImage}, #{schoolDegree}, #{productStatus},'审核中')")
 @Options(useGeneratedKeys = true, keyProperty = "productId", keyColumn = "Product_id")
 void insertProduct(productsInfo product);

 @Select("SELECT * FROM tayun.products WHERE Product_name LIKE CONCAT('%', #{keyword}, '%')")
 List<productsInfo> getProductByKeyword(String keyword);

 @Select("SELECT category, COUNT(*) AS product_count FROM products GROUP BY category")
 List<Map<String, Object>> getEchartCake();

}
